USE [DSRSystem_Temp]
GO

/****** Object:  StoredProcedure [dbo].[spUpdateFinalizedIndicators]    Script Date: 1/26/2016 12:56:37 PM ******/
DROP PROCEDURE [dbo].[spUpdateFinalizedIndicators]
GO

/****** Object:  StoredProcedure [dbo].[spUpdateFinalizedIndicators]    Script Date: 1/26/2016 12:56:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[spUpdateFinalizedIndicators]
@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	declare @startTime datetime
	declare @endTime datetime
	declare @elapsedTime int
	declare @rowsAffected int
	declare @Log table
	(
		Description nvarchar(max) NOT NULL,
		ElapsedTime int NOT NULL,
		RowsAffected int NOT NULL
	)

	select @startTime = getdate()

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)


	DELETE Indicators FROM dbo.Indicators AS Indicators
	LEFT OUTER JOIN dbo.FinalizedCompanies AS FinalizedCompanies ON Indicators.CompanyId = FinalizedCompanies.CompanyId
	WHERE  (FinalizedCompanies.CompanyId IS NULL)   
	
	set @rowsAffected = @@ROWCOUNT
	select @endTime = getdate()
	select @elapsedTime = datediff(second, @startTime, @endTime)
	insert into @Log (Description, ElapsedTime, RowsAffected)
	values ('Delete indicators that do not belong to current companies', @elapsedTime, @rowsAffected)
	select @startTime = getdate()


	DELETE Indicators FROM dbo.Indicators AS Indicators
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON Indicators.CompanyId = SelectedCompanies.Id

	set @rowsAffected = @@ROWCOUNT
	select @endTime = getdate()
	select @elapsedTime = datediff(second, @startTime, @endTime)
	insert into @Log (Description, ElapsedTime, RowsAffected)
	values ('Delete indicators that are going to be updated', @elapsedTime, @rowsAffected)
	select @startTime = getdate()


	INSERT INTO [dbo].[Indicators]
	SELECT * FROM [dbo].[StageIndicators]

	set @rowsAffected = @@ROWCOUNT
	select @endTime = getdate()
	select @elapsedTime = datediff(second, @startTime, @endTime)
	print 'Indicators have been updated => Elapsed time:' +  str(@elapsedTime) + ', RowsAffected:' + str(@rowsAffected)


	SELECT * FROM @Log
END

GO


